﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using BestBuy.DAL;
using System.Data;

namespace BestBuy.DAL
{
    public class CDanhMuc
    {
        private TradeKeyDataContext context = new TradeKeyDataContext();
        private SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["TradeKeyConnectionString"].ConnectionString);
        public CDanhMuc() { }

        public int[] FilterMaDuLieu()
        {
            DataTable dt = new DataTable();
            int[] arrMaDuLieu = new int[20];
            string sql = "select top(20) dl.IDDanhMuc,COUNT(dl.IDDanhMuc) as row_num from SanPham dl " +
                        "group by dl.IDDanhMuc " +
                        "order by COUNT(dl.IDDanhMuc) desc";
            sqlCon.Open();
            SqlCommand cmd = new SqlCommand(sql, sqlCon);
            SqlDataReader dr = cmd.ExecuteReader();
            int i = 0;
            while (dr.Read())
            {
                arrMaDuLieu[i] = int.Parse(dr["IDDanhMuc"].ToString());
                i++;
            }

            sqlCon.Close();

            return arrMaDuLieu;
        }

        public List<DanhMuc> GetListFeatured()
        {
            int[] arrMaDuLieu = this.FilterMaDuLieu();

            var result = (from dl in context.DanhMucs
                          where arrMaDuLieu.Contains(dl.IDDanhMuc)
                          select dl).ToList();
            return result;
        }

        public List<DanhMuc> GetListCataLog(){
            var q = (from sp in context.DanhMucs 
                     where sp.TinhTrang == true
                     orderby sp.TenDanhMuc ascending
                     select sp).ToList();
            return q;
        }

        public DanhMuc GetByID(int id) {
            var q = (from sp in context.DanhMucs 
                     where sp.IDDanhMuc == id && sp.TinhTrang == true
                     select sp).FirstOrDefault();
            return q;
        }

        public List<DanhMuc> GetListCataLogID(int id) { 
            var q = (from sp in context.DanhMucs
                     where sp.IDDanhMuc != id 
                     && sp.TinhTrang == true 
                     && (sp.IDDanhMucCha != id || sp.IDDanhMucCha == null)
                     && sp.SanPhams.Count == 0
                     && sp.DanhMucBans.Count == 0
                     && sp.DanhMucMuas.Count == 0
                     && sp.DanhMucCongTies.Count == 0
                     orderby sp.TenDanhMuc ascending
                     select sp).ToList();
            return q;
        }

        public List<DanhMuc> GetListCataLogAddParent()
        {
            var q = (from sp in context.DanhMucs
                     where sp.TinhTrang == true
                     && sp.SanPhams.Count == 0
                     && sp.DanhMucBans.Count == 0
                     && sp.DanhMucMuas.Count == 0
                     && sp.DanhMucCongTies.Count == 0
                     orderby sp.TenDanhMuc ascending
                     select sp).ToList();
            return q;
        }

        public List<DanhMuc> GetListCataLogIndustry() {
            var q = (from sp in context.DanhMucs
                     where sp.TinhTrang == true && sp.IDDanhMucCha == null
                     orderby sp.TenDanhMuc ascending
                     select sp).ToList();
            return q;
        }
        public int GetQuantityOfCategoryProduct(int parentID)
        {
            int quantity = 0;
            var result = context.DanhMucs.Where(u => u.IDDanhMucCha == parentID).ToList();
            foreach (DanhMuc dm in result)
            {
                quantity += dm.SanPhams.Count;

                var temp = context.DanhMucs.Where(p => p.IDDanhMucCha == dm.IDDanhMuc).ToList();
                foreach (DanhMuc t in temp)
                {
                    quantity += t.SanPhams.Count;
                }
            }

            return quantity;
        }

        public int GetQuantityOfCategoryBuy(int parentID)
        {
            int quantity = 0;
            var result = context.DanhMucs.Where(u => u.IDDanhMucCha == parentID).ToList();
            foreach (DanhMuc dm in result)
            {
                quantity += dm.DanhMucMuas.Count;

                var temp = context.DanhMucs.Where(p => p.IDDanhMucCha == dm.IDDanhMuc).ToList();
                foreach (DanhMuc t in temp)
                {
                    quantity += t.DanhMucMuas.Count;
                }
            }

            return quantity;
        }

        public int GetQuantityOfCatDistinctBuyOffer(int rootID)
        {
            List<int> listID = new List<int>();
            listID.Add(rootID);//add category level1
            listID.AddRange(context.DanhMucs.Where(u => listID.Contains(u.IDDanhMucCha.Value) && u.TinhTrang == true)
                .Select(u => u.IDDanhMuc).ToList()); //add category level2
            listID.AddRange(context.DanhMucs.Where(u => listID.Contains(u.IDDanhMucCha.Value) && u.TinhTrang == true)
                .Select(u => u.IDDanhMuc).ToList()); //add category level3
            var result = context.DanhMucMuas.Where(u => listID.Contains(u.IDDanhMuc)).Select(u => u.IDBangTinMua).Distinct().ToList();
            return result.Count;
        }

        public int GetQuantityOfCategorySell(int parentID)
        {
            int quantity = 0;
            var result = context.DanhMucs.Where(u => u.IDDanhMucCha == parentID).ToList();
            foreach (DanhMuc dm in result)
            {
                quantity += dm.DanhMucBans.Count;

                var temp = context.DanhMucs.Where(p => p.IDDanhMucCha == dm.IDDanhMuc).ToList();
                foreach (DanhMuc t in temp)
                {
                    quantity += t.DanhMucBans.Count;
                }
            }
            return quantity;
        }

        public int GetQuantityOfCatDistinctSellOffer(int rootID)
        {
            List<int> listID = new List<int>();
            listID.Add(rootID);//add category level1
            listID.AddRange(context.DanhMucs.Where(u => listID.Contains(u.IDDanhMucCha.Value) && u.TinhTrang == true)
                .Select(u => u.IDDanhMuc).ToList()); //add category level2
            listID.AddRange(context.DanhMucs.Where(u => listID.Contains(u.IDDanhMucCha.Value) && u.TinhTrang == true)
                .Select(u => u.IDDanhMuc).ToList()); //add category level3
            var result = context.DanhMucBans.Where(u => listID.Contains(u.IDDanhMuc)).Select(u=>u.IDBangTinBan).Distinct().ToList();
            return result.Count;
        }

        public int GetQuantityOfCatDistinctCompany(int rootID)
        {
            List<int> listID = new List<int>();
            listID.Add(rootID);//add category level1
            listID.AddRange(context.DanhMucs.Where(u => listID.Contains(u.IDDanhMucCha.Value) && u.TinhTrang == true)
                .Select(u => u.IDDanhMuc).ToList()); //add category level2
            listID.AddRange(context.DanhMucs.Where(u => listID.Contains(u.IDDanhMucCha.Value) && u.TinhTrang == true)
                .Select(u => u.IDDanhMuc).ToList()); //add category level3
            var result = context.DanhMucCongTies.Where(u => listID.Contains(u.IDDanhMuc)).Select(u => u.IDCongTy).Distinct().ToList();
            return result.Count;
        }        

        public List<DanhMuc> GetChildrenOfCategory(int parentID, int start, int end)
        {
            var result = context.DanhMucs.Where(p => p.IDDanhMucCha == parentID && p.TinhTrang==true).Skip(start-1).Take(end).ToList();
            return result;
        }
        public List<DanhMuc> GetChildrenOfCategory(int parentID, int start)
        {
            return context.DanhMucs.Where(p => p.IDDanhMucCha == parentID && p.TinhTrang == true).Skip(start-1).ToList();
        }
        public bool CheckCategoryChildrenMoreThan5(int parentID)
        {
            int sl = context.DanhMucs.Where(p => p.IDDanhMucCha == parentID && p.TinhTrang == true).Count();
            if (sl > 5)
                return true;
            return false;
        }
        public string GetImageOfCateogry(int ID)
        {
            string strImg = "";
            var result = context.DanhMucs.Where(u => u.IDDanhMucCha == ID && u.TinhTrang == true).ToList();
            foreach(DanhMuc item in result)
            {
                strImg = item.SanPhams.Where(u => u.HinhAnh != null).Select(u => u.HinhAnh).FirstOrDefault().ToString();
                if (strImg != "")
                    return strImg;
            }
            return strImg;
        }

        public bool AddCatalog(DanhMuc dm) {
            bool kq = this.CheckCatalog(dm.TenDanhMuc);
            if (kq)
            {
                try
                {
                    context.DanhMucs.InsertOnSubmit(dm);
                    context.SubmitChanges();
                }
                catch
                {
                    return false;
                }
            }
            else {
                return false;
            }
            return true;
        }

        public bool CheckCatalog(string name) {
            var q = (from sp in context.DanhMucs
                     where sp.TenDanhMuc.Equals(name)
                     select sp).FirstOrDefault();
            if (q != null) {
                return false;
            }
            return true;
        }
		
		public bool CheckCatalogUpdate(string name,int id){
			var q = (from sp in context.DanhMucs
                     where sp.TenDanhMuc.Equals(name) && sp.IDDanhMuc != id
                     select sp).FirstOrDefault();
            if (q != null) {
                return false;
            }
            return true;
		}
		
        public bool UpdateCatalog(DanhMuc dm) {
            bool kq = this.CheckCatalogUpdate(dm.TenDanhMuc,dm.IDDanhMuc);
            if (kq)
            {
                var q = (from sp in context.DanhMucs
                         where sp.IDDanhMuc == dm.IDDanhMuc
                         select sp).FirstOrDefault();
                if (q != null)
                {
                    q.TenDanhMuc = dm.TenDanhMuc;
                    q.IDDanhMucCha = dm.IDDanhMucCha;
                    q.TinhTrang = dm.TinhTrang;
                    try
                    {
                        context.SubmitChanges();
                    }
                    catch
                    {
                        return false;
                    }
                }
            }
            else {
                return false;
            }

            return true;
        }

        public bool DeleteCatalog(int id)
        {
            var q = (from sp in context.DanhMucs
                     where sp.IDDanhMuc == id
                     select sp).FirstOrDefault();
            if (q != null)
            {
                q.TinhTrang = false;
                try
                {
                    context.SubmitChanges();
                }
                catch
                {
                    return false;
                }
            }
            return true;
        }

    }
}